package com.ruoyi.crm.service.impl;

import com.ruoyi.common.exception.UtilException;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.spring.SpringUtils;
import com.ruoyi.crm.domain.vo.ReportVo;
import com.ruoyi.crm.service.ReportType;
import lombok.Data;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.text.DecimalFormat;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 简单实现自定义报表单表查询，后期计划支持多表关联
 */
@Service
@Slf4j
public class ReportService {
    public static String SQL_REGEX = "select |insert |delete |update |drop |exec |chr |mid |master |truncate |char |and |declare ";

    private static void filterKeyword(ReportVo reportVo) {
        filterKeyword(reportVo.getDateField());
        filterKeyword(reportVo.getTable());
        reportVo.getAvgFields().forEach(ReportService::filterKeyword);
        reportVo.getSumFields().forEach(ReportService::filterKeyword);
        reportVo.getMaxFields().forEach(ReportService::filterKeyword);
        reportVo.getMinFields().forEach(ReportService::filterKeyword);
        reportVo.getCountFields().forEach(ReportService::filterKeyword);
        reportVo.getColumns().forEach(ReportService::filterKeyword);
    }

    public static void filterKeyword(String value) {
        if (StringUtils.isEmpty(value)) {
            return;
        }
        String[] sqlKeywords = StringUtils.split(SQL_REGEX, "\\|");
        for (String sqlKeyword : sqlKeywords) {
            if (StringUtils.indexOfIgnoreCase(value, sqlKeyword) > -1) {
                throw new UtilException("参数存在SQL注入风险");
            }
        }
    }

    public static void main(String[] args) {
        String sql = "select dept_id,year(create_time) as 'year',month(create_time) AS `date`";
        String pattern = "(AS|as)\\s+((`\\w+`)|('\\w+'))";
        sql = sql.replaceAll(pattern, "");
        System.out.println(sql);
        LocalDateTime localDateTime = LocalDateTime.parse("2020-01-10 10:00:00",
                DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))
                .with(LocalTime.MAX);
        String format = localDateTime.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        System.out.println(format);
    }

    @SneakyThrows
    public List<LinkedHashMap<String, Object>> getData(ReportVo reportVo) {
        // 防止sql注入
        filterKeyword(reportVo);
        // 设置开始日期时间为00:00:00，结束日期时间为23:59:59
        Date from = Date.from(LocalDateTime.parse(reportVo.getFrom(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")).with(LocalTime.MIN).atZone(ZoneId.systemDefault()).toInstant());
        Date to = Date.from(LocalDateTime.parse(reportVo.getTo(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")).with(LocalTime.MAX).atZone(ZoneId.systemDefault()).toInstant());
        // 构造sql
        String sql = new ReportSqlBuilder(reportVo).buildSql();
        // jdbc执行sql，返回结果
        return executeSql(Arrays.asList(from, to), sql, reportVo.getColumns());
    }

    /**
     * 通过jdbc执行查询，获取数据
     * @param paramsList where条件参数
     * @param sql 拼装好的sql语句
     * @param columns select中选择的列，不包括聚合列
     * @return
     * @throws SQLException
     */
    private List<LinkedHashMap<String, Object>> executeSql(List<Object> paramsList, String sql, List<String> columns) throws SQLException {
        List<LinkedHashMap<String, Object>> resultList = null;
        SqlSession sqlSession = null;
        Connection connection = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        if (log.isDebugEnabled()) {
            log.debug(sql);
            log.debug("paramsList{}", paramsList);
        }
        try {
            SqlSessionFactory sqlSessionFactory = SpringUtils.getBean(SqlSessionFactory.class);
            sqlSession = sqlSessionFactory.openSession();
            connection = sqlSession.getConnection();
            pstmt = connection.prepareStatement(sql);
            for (int i = 0; i < paramsList.size(); i++) {
                pstmt.setObject(i + 1, paramsList.get(i));
            }
            rs = pstmt.executeQuery();
            resultList = parseResultSet(rs, columns);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            rs.close();
            pstmt.close();
            connection.close();
            sqlSession.close();
        }
        return resultList;
    }

    /**
     * 遍历结果集，每一条数据转换成map
     * @param rs
     * @param columns
     * @return
     * @throws SQLException
     */
    private List<LinkedHashMap<String, Object>> parseResultSet(ResultSet rs, List<String> columns) throws SQLException {
        List<LinkedHashMap<String, Object>> resultList = new ArrayList<>();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        while (rs.next()) {
            Map<String, Object> rowMap = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                rowMap.put(metaData.getColumnName(i), rs.getObject(i));
            }
            LinkedHashMap<String, Object> sortMap =  customizeSort(rowMap, columns);
            resultList.add(sortMap);
        }
        if (log.isDebugEnabled()) {
            log.debug("resultList:{}", resultList);
        }
        Collections.sort(resultList, (o1, o2) -> {
            String date1 = o1.getOrDefault("year", "").toString() + o1.getOrDefault("date", "").toString();
            String date2 = o2.getOrDefault("year", "").toString() + o2.getOrDefault("date", "").toString();
            return date1.compareTo(date2);
        });
        return resultList;
    }

    /**
     * 自定义列的显示顺序，方便前端table动态循环显示
     * @param rowMap
     * @param columns
     * @return
     */
    private LinkedHashMap<String, Object> customizeSort(Map<String, Object> rowMap, List<String> columns) {
        LinkedHashMap<String, Object> linkedHashMap = new LinkedHashMap<>();
        // 先添加年和日期
        if(rowMap.get("year") != null) {
            linkedHashMap.put("year", rowMap.get("year"));
        }
        if(rowMap.get("date") != null) {
            linkedHashMap.put("date", rowMap.get("date"));
        }
        // 添加普通列
        columns.forEach(item -> linkedHashMap.put(item, rowMap.get(item)));
        // 添加统计列
        Set<String> allColumns = rowMap.keySet();
        List<String> aggregationColumns = new ArrayList<>();
        allColumns.stream().filter(item -> item.startsWith("sum_")).forEach(aggregationColumns::add);
        allColumns.stream().filter(item -> item.startsWith("avg_")).forEach(aggregationColumns::add);
        allColumns.stream().filter(item -> item.startsWith("max_")).forEach(aggregationColumns::add);
        allColumns.stream().filter(item -> item.startsWith("min_")).forEach(aggregationColumns::add);
        allColumns.stream().filter(item -> item.startsWith("count_")).forEach(aggregationColumns::add);
        aggregationColumns.forEach(item -> linkedHashMap.put(item, rowMap.get(item)));
        return linkedHashMap;
    }

    @Data
    private static class ReportSqlBuilder {
        /**
         * select中需要统计的列名，支持count,sum,avg,max,min，需要指定表别名
         */
        private List<String> maxFields = new ArrayList<>();
        private List<String> minFields = new ArrayList<>();
        private List<String> avgFields = new ArrayList<>();
        private List<String> sumFields = new ArrayList<>();
        private List<String> countFields = new ArrayList<>();
        /**
         * select中选取的列名，这些列也会出现在group by的字段列表
         */
        private List<String> columns = new ArrayList<>();
        /**
         * 表名，可以多表连接查询，第一张表别名a，第二张表别名b，依此类推
         */
        private String table;
        /**
         * 日期字段，使用该字段作为日期范围查询的条件
         */
        private String dateField;
        /**
         * 日报/周报/月报/季报/年报
         */
        private ReportType reportType;

        public ReportSqlBuilder(ReportVo reportVo) {
            this.columns.addAll(reportVo.getColumns());
            this.maxFields.addAll(reportVo.getMaxFields());
            this.minFields.addAll(reportVo.getMinFields());
            this.avgFields.addAll(reportVo.getAvgFields());
            this.sumFields.addAll(reportVo.getSumFields());
            this.countFields.addAll(reportVo.getCountFields());
            this.reportType = ReportType.valueOf(reportVo.getReportType());
            this.table = reportVo.getTable();
            this.dateField = reportVo.getDateField();
        }

        public void addColumns(String... columns) {
            for (String column : columns) {
                this.columns.add(column);
            }
        }

        public String buildSql() {
            StringBuilder sql = new StringBuilder();
            String year = "year(" + dateField + ") AS `year`";
            switch (reportType) {
                case DATE:
                    addColumns(year, "date(" + dateField + ") AS `date`");
                    break;
                case WEEK:
                    addColumns(year, "week(" + dateField + ") AS `date`");
                    break;
                case MONTH:
                    addColumns(year, "month(" + dateField + ") AS `date`");
                    break;
                case QUARTER:
                    addColumns(year, "quarter(" + dateField + ") AS `date`");
                    break;
                case YEAR:
                    addColumns(year);
                    break;
            }
            sql.append("select ");
            String originalSelectColumns = columns.stream().collect(Collectors.joining(","));
            String selectColumns = buildJoinColumns(originalSelectColumns, table);
            sql.append(selectColumns);
            buildAggregationColumns(sql, "sum", sumFields);
            buildAggregationColumns(sql, "max", maxFields);
            buildAggregationColumns(sql, "min", minFields);
            buildAggregationColumns(sql, "avg", avgFields);
            buildAggregationColumns(sql, "count", countFields);
            sql.append(" from ");
            sql.append(table);
            sql.append(" where ");
            sql.append(dateField).append(" >= ? and ")
                    .append(dateField).append(" <= ?");
            sql.append(" group by ");
            // 删除分组中的列别名
            String pattern = "(AS|as)\\s+((`\\w+`)|('\\w+'))";
            originalSelectColumns = originalSelectColumns.replaceAll(pattern, "");
            sql.append(originalSelectColumns);
            return sql.toString();
        }

        /**
         * 替换需要关联查询的列
         * @param originalSelectColumns
         * @return
         */
        private String buildJoinColumns(String originalSelectColumns, String table) {
            for (Map.Entry<String, String> entry : joinColumnMap.entrySet()) {
                originalSelectColumns = originalSelectColumns.replace(entry.getKey(), entry.getValue());
            }
            originalSelectColumns = originalSelectColumns.replace("{TABLE}", table);
            return originalSelectColumns;
        }

        /**
         * 给聚合函数的列名前加上函数名字, 例如 sum(order_qty) 转换成 sum(order_qty) AS `sum_order_qty`
         * @param sql
         * @param functionName
         * @param fields
         */
        private static void buildAggregationColumns(StringBuilder sql, String functionName, List<String> fields) {
            if(!fields.isEmpty()) {
                sql.append(",");
                sql.append(fields.stream().map(item -> functionName + "(" + item + ") AS `" + functionName + "_" + item + "`").collect(Collectors.joining(",")));
            }
        }

        // 从关联表根据id获取对应的名字
        public static Map<String, String> joinColumnMap = new HashMap<>();
        static {
            joinColumnMap.put("dept_id", "(SELECT dept_name from sys_dept where dept_id = {TABLE}.dept_id) as dept_id");
            joinColumnMap.put("create_by", "(SELECT nick_name from sys_user where user_name = {TABLE}.create_by) as create_by");
            joinColumnMap.put("channel", "(SELECT dict_label from sys_dict_data where dict_value = {TABLE}.channel and dict_type='clues_item') as channel");
            joinColumnMap.put("course_id", "(SELECT `name` from tb_course where id = {TABLE}.course_id) as course_id");
            joinColumnMap.put("sex", "(SELECT dict_label from sys_dict_data where dict_value = {TABLE}.sex and dict_type='sys_user_sex') as sex");
            joinColumnMap.put("activity_id", "(SELECT name from tb_activity where id = {TABLE}.activity_id) as activity_id");
            joinColumnMap.put("subject", "(SELECT dict_label from sys_dict_data where dict_value = {TABLE}.subject and dict_type='course_subject') as subject");
        }
    }
}
